const { pool } = require('../config/database')

class User {
  constructor(data = {}) {
    this.id = data.id || null
    this.username = data.username || ''
    this.password = data.password || ''
    this.real_name = data.real_name || ''
    this.phone = data.phone || ''
    this.role = data.role || 'user'
    this.last_login_at = data.last_login_at || null
  }

  // 创建用户
  static async create(userData) {
    try {
      const { username, password, real_name, phone, role } = userData
      const [result] = await pool.execute(
        'INSERT INTO user (username, password, real_name, phone, role, last_login_at) VALUES (?, ?, ?, ?, ?, NOW())',
        [username, password, real_name, phone, role || 'user'],
      )

      if (result.insertId) {
        return await User.findById(result.insertId)
      }
      return null
    } catch (error) {
      throw new Error(`创建用户失败: ${error.message}`)
    }
  }

  // 根据ID查找用户
  static async findById(id) {
    try {
      const [rows] = await pool.execute('SELECT * FROM user WHERE id = ?', [id])
      return rows.length > 0 ? new User(rows[0]) : null
    } catch (error) {
      throw new Error(`查找用户失败: ${error.message}`)
    }
  }

  // 根据用户名查找用户
  static async findByUsername(username) {
    try {
      const [rows] = await pool.execute('SELECT * FROM user WHERE username = ?', [username])
      return rows.length > 0 ? new User(rows[0]) : null
    } catch (error) {
      throw new Error(`查找用户失败: ${error.message}`)
    }
  }

  // 获取所有用户（分页）
  static async findAll(page = 1, limit = 10, filters = {}) {
    try {
      // 确保 page 和 limit 是有效的正整数
      const safePage = Math.max(1, parseInt(page) || 1)
      const safeLimit = Math.max(1, Math.min(100, parseInt(limit) || 10)) // 限制最大100条
      const offset = (safePage - 1) * safeLimit
      
      let whereClause = ''
      let filterParams = []

      // 构建WHERE条件
      if (filters.username) {
        whereClause += ' WHERE username LIKE ?'
        filterParams.push(`%${filters.username}%`)
      }
      if (filters.role) {
        whereClause += whereClause ? ' AND role = ?' : ' WHERE role = ?'
        filterParams.push(filters.role)
      }

      // 获取总数（只用 filterParams）
      const [countResult] = await pool.execute(`SELECT COUNT(*) as total FROM user${whereClause}`, filterParams)
      const total = countResult[0].total

      // 获取数据 - 使用 LIMIT count OFFSET offset 语法更清晰
      const [rows] = await pool.execute(
        `SELECT * FROM user${whereClause} ORDER BY id DESC LIMIT ? OFFSET ?`, 
        [...filterParams, String(safeLimit), String(offset)]
      )

      const users = rows.map((row) => new User(row))

      return {
        data: users,
        pagination: {
          current: safePage,
          pageSize: safeLimit,
          total,
          totalPages: Math.ceil(total / safeLimit),
        },
      }
    } catch (error) {
      throw new Error(`获取用户列表失败: ${error.message}`)
    }
  }

  // 更新用户
  static async updateById(id, updateData) {
    try {
      const fields = []
      const values = []

      // 构建更新字段
      Object.keys(updateData).forEach((key) => {
        if (updateData[key] !== undefined && key !== 'id') {
          fields.push(`${key} = ?`)
          values.push(updateData[key])
        }
      })

      if (fields.length === 0) {
        throw new Error('没有要更新的字段')
      }

      values.push(id)

      const [result] = await pool.execute(`UPDATE user SET ${fields.join(', ')} WHERE id = ?`, values)

      if (result.affectedRows > 0) {
        return await User.findById(id)
      }
      return null
    } catch (error) {
      throw new Error(`更新用户失败: ${error.message}`)
    }
  }

  // 删除用户
  static async deleteById(id) {
    try {
      const [result] = await pool.execute('DELETE FROM user WHERE id = ?', [id])
      return result.affectedRows > 0
    } catch (error) {
      throw new Error(`删除用户失败: ${error.message}`)
    }
  }

  // 批量删除用户
  static async deleteByIds(ids) {
    try {
      if (!Array.isArray(ids) || ids.length === 0) {
        throw new Error('ID数组不能为空')
      }

      const placeholders = ids.map(() => '?').join(',')
      const [result] = await pool.execute(`DELETE FROM user WHERE id IN (${placeholders})`, ids)

      return result.affectedRows
    } catch (error) {
      throw new Error(`批量删除用户失败: ${error.message}`)
    }
  }

  // 更新最后登录时间
  static async updateLastLogin(id) {
    try {
      const [result] = await pool.execute('UPDATE user SET last_login_at = NOW() WHERE id = ?', [id])
      return result.affectedRows > 0
    } catch (error) {
      throw new Error(`更新登录时间失败: ${error.message}`)
    }
  }

  // 检查用户名是否存在
  static async isUsernameExists(username, excludeId = null) {
    try {
      let query = 'SELECT COUNT(*) as count FROM user WHERE username = ?'
      let params = [username]

      if (excludeId) {
        query += ' AND id != ?'
        params.push(excludeId)
      }

      const [rows] = await pool.execute(query, params)
      return rows[0].count > 0
    } catch (error) {
      throw new Error(`检查用户名失败: ${error.message}`)
    }
  }

  // 转换为JSON对象（排除敏感信息）
  toJSON() {
    const { password, ...userWithoutPassword } = this
    return userWithoutPassword
  }
}

module.exports = User
